package com.scuec.groupfive.mapper;

import com.scuec.groupfive.vo.SubjectFinishedVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SubjectFinishedMapper {
    @Select("SELECT\n" +
            "    s.subject_name AS subject,\n" +
            "    COALESCE(aq.total_answered, 0) AS answeredCount,\n" +
            "    ROUND(\n" +
            "        COALESCE(\n" +
            "            oa.correct_count * 100.0 / NULLIF(oa.total_objective, 0), \n" +
            "        0.00), 2\n" +
            "    ) AS correctRate,\n" +
            "    ROUND(\n" +
            "        COALESCE(\n" +
            "            aq.total_answered * 100.0 / NULLIF(tq.total_questions, 0), \n" +
            "        0.00), 2\n" +
            "    ) AS progressRate,\n" +
            "    MAX(la.last_active_time) AS lastActiveTime\n" +
            "FROM subjects s\n" +
            "-- 已做题数统计（按科目分组）\n" +
            "LEFT JOIN (\n" +
            "    SELECT \n" +
            "        q.subject_id,\n" +
            "        COUNT(DISTINCT ar.question_id) AS total_answered\n" +
            "    FROM answer_records ar\n" +
            "    JOIN questions q ON ar.question_id = q.question_id\n" +
            "    WHERE ar.user_id = #{userId}\n" +
            "    GROUP BY q.subject_id\n" +
            ") aq ON s.subject_id = aq.subject_id\n" +
            "-- 客观题正确率统计（按科目分组+题目去重）\n" +
            "LEFT JOIN (\n" +
            "    SELECT \n" +
            "        q.subject_id,\n" +
            "        COUNT(DISTINCT CASE WHEN ar.is_correct THEN ar.question_id END) AS correct_count,\n" +
            "        COUNT(DISTINCT ar.question_id) AS total_objective\n" +
            "    FROM answer_records ar\n" +
            "    JOIN questions q ON ar.question_id = q.question_id\n" +
            "    WHERE ar.user_id = #{userId} \n" +
            "      AND ar.question_type IN ('single_choice', 'fill_in_the_blank')\n" +
            "    GROUP BY q.subject_id\n" +
            ") oa ON s.subject_id = oa.subject_id\n" +
            "-- 科目总题数统计（正常状态题目）\n" +
            "LEFT JOIN (\n" +
            "    SELECT \n" +
            "        subject_id,\n" +
            "        COUNT(*) AS total_questions\n" +
            "    FROM questions\n" +
            "    WHERE status = 1 \n" +
            "    GROUP BY subject_id\n" +
            ") tq ON s.subject_id = tq.subject_id\n" +
            "-- 最近活跃时间统计\n" +
            "LEFT JOIN (\n" +
            "    SELECT \n" +
            "        q.subject_id,\n" +
            "        MAX(ar.answered_at) AS last_active_time\n" +
            "    FROM answer_records ar\n" +
            "    JOIN questions q ON ar.question_id = q.question_id\n" +
            "    WHERE ar.user_id = #{userId}\n" +
            "    GROUP BY q.subject_id\n" +
            ") la ON s.subject_id = la.subject_id\n" +
            "GROUP BY s.subject_id, s.subject_name\n" +
            "ORDER BY s.subject_id")
    List<SubjectFinishedVO> getSubjectFinishedInfo(@Param("userId") Integer userId);
}